import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;

public class StudentScoreManagementSystemGUI {

    // 数据库连接信息
    private static final String DB_URL = "jdbc:kingbase8://localhost:54321/test";
    private static final String DB_USER = "system";
    private static final String DB_PASSWORD = "wangkanglei666";

    // 用于存储学生成绩数据的模型
    private DefaultListModel<StudentScore> studentScoreListModel = new DefaultListModel<>();
    private JTextField studentIdTextField, studentNameTextField, studentScoreTextField;
    private JList<StudentScore> studentScoreList;

    public static void main(String[] args) {
        SwingUtilities.invokeLater(() -> {
            new StudentScoreManagementSystemGUI().createAndShowGUI();
        });
    }

    // 创建并显示GUI界面
    private void createAndShowGUI() {
        JFrame frame = new JFrame("学生成绩管理系统");
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        frame.setLayout(new BorderLayout(10, 10));

        // 创建插入学生成绩界面
        studentIdTextField = new JTextField(15);
        studentNameTextField = new JTextField(15);
        studentScoreTextField = new JTextField(15);
        JButton insertButton = new JButton("插入学生成绩");

        // 设置插入按钮事件
        insertButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                insertStudentScore();
            }
        });

        JPanel insertPanel = new JPanel();
        insertPanel.setLayout(new GridLayout(4, 2, 10, 10));
        insertPanel.add(new JLabel("学号："));
        insertPanel.add(studentIdTextField);
        insertPanel.add(new JLabel("姓名："));
        insertPanel.add(studentNameTextField);
        insertPanel.add(new JLabel("成绩："));
        insertPanel.add(studentScoreTextField);
        insertPanel.add(insertButton);

        // 创建操作面板（查询、修改、删除、退出）
        JPanel actionPanel = new JPanel();
        actionPanel.setLayout(new GridLayout(4, 1, 10, 10));
        actionPanel.add(createSearchButton());
        actionPanel.add(createUpdateButton());
        actionPanel.add(createDeleteButton());
        actionPanel.add(createExitButton());

        // 创建展示成绩的JList
        studentScoreList = new JList<>(studentScoreListModel);
        JScrollPane scrollPane = new JScrollPane(studentScoreList);
        scrollPane.setPreferredSize(new Dimension(350, 300));

        // 布局
        JPanel centerPanel = new JPanel();
        centerPanel.setLayout(new BorderLayout(10, 10));
        centerPanel.add(insertPanel, BorderLayout.NORTH);
        centerPanel.add(actionPanel, BorderLayout.CENTER);

        // 布局调整，确保左右两侧合理分配
        frame.add(centerPanel, BorderLayout.WEST);
        frame.add(scrollPane, BorderLayout.EAST);

        // 初始加载所有学生成绩
        loadAllStudentScores();

        // 设置窗口
        frame.setSize(800, 500);
        frame.setVisible(true);
    }

    // 插入学生成绩
    private void insertStudentScore() {
        String studentId = studentIdTextField.getText();
        String studentName = studentNameTextField.getText();
        double score;
        try {
            score = Double.parseDouble(studentScoreTextField.getText());
        } catch (NumberFormatException e) {
            JOptionPane.showMessageDialog(null, "请输入有效的成绩！");
            return;
        }

        try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            String insertQuery = "INSERT INTO student_scores (student_id, student_name, score) VALUES (?, ?, ?)";
            try (PreparedStatement statement = connection.prepareStatement(insertQuery)) {
                statement.setString(1, studentId);
                statement.setString(2, studentName);
                statement.setDouble(3, score);
                int rowsAffected = statement.executeUpdate();
                if (rowsAffected > 0) {
                    loadAllStudentScores(); // 插入成功后刷新数据
                    JOptionPane.showMessageDialog(null, "学生成绩插入成功！");
                } else {
                    JOptionPane.showMessageDialog(null, "插入失败，请检查输入！");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 查询学生成绩
    private JButton createSearchButton() {
        JButton searchButton = new JButton("查询成绩");
        searchButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                String studentId = JOptionPane.showInputDialog("请输入学号查询成绩：");
                queryStudentScore(studentId);
            }
        });
        return searchButton;
    }

    // 查询成绩的实现
    private void queryStudentScore(String studentId) {
        try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            String query = "SELECT * FROM student_scores WHERE student_id = ?";
            try (PreparedStatement statement = connection.prepareStatement(query)) {
                statement.setString(1, studentId);
                ResultSet resultSet = statement.executeQuery();
                studentScoreListModel.clear();
                if (resultSet.next()) {
                    String studentName = resultSet.getString("student_name");
                    double score = resultSet.getDouble("score");
                    studentScoreListModel.addElement(new StudentScore(studentId, studentName, score));
                } else {
                    JOptionPane.showMessageDialog(null, "未找到该学号的学生成绩！");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 更新学生成绩
    private JButton createUpdateButton() {
        JButton updateButton = new JButton("修改成绩");
        updateButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                String studentId = JOptionPane.showInputDialog("请输入学号修改成绩：");
                String newScoreStr = JOptionPane.showInputDialog("请输入新的成绩：");
                try {
                    double newScore = Double.parseDouble(newScoreStr);
                    updateStudentScore(studentId, newScore);
                } catch (NumberFormatException ex) {
                    JOptionPane.showMessageDialog(null, "请输入有效的成绩！");
                }
            }
        });
        return updateButton;
    }

    // 更新成绩的实现
    private void updateStudentScore(String studentId, double newScore) {
        try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            String updateQuery = "UPDATE student_scores SET score = ? WHERE student_id = ?";
            try (PreparedStatement statement = connection.prepareStatement(updateQuery)) {
                statement.setDouble(1, newScore);
                statement.setString(2, studentId);
                int rowsAffected = statement.executeUpdate();
                if (rowsAffected > 0) {
                    loadAllStudentScores(); // 更新成功后刷新数据
                    JOptionPane.showMessageDialog(null, "成绩更新成功！");
                } else {
                    JOptionPane.showMessageDialog(null, "未找到该学号的学生成绩！");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 删除学生成绩
    private JButton createDeleteButton() {
        JButton deleteButton = new JButton("删除成绩");
        deleteButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                String studentId = JOptionPane.showInputDialog("请输入学号删除成绩：");
                deleteStudentScore(studentId);
            }
        });
        return deleteButton;
    }

    // 删除成绩的实现
    private void deleteStudentScore(String studentId) {
        try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            String deleteQuery = "DELETE FROM student_scores WHERE student_id = ?";
            try (PreparedStatement statement = connection.prepareStatement(deleteQuery)) {
                statement.setString(1, studentId);
                int rowsAffected = statement.executeUpdate();
                if (rowsAffected > 0) {
                    loadAllStudentScores(); // 删除成功后刷新数据
                    JOptionPane.showMessageDialog(null, "成绩删除成功！");
                } else {
                    JOptionPane.showMessageDialog(null, "未找到该学号的学生成绩！");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 退出按钮
    private JButton createExitButton() {
        JButton exitButton = new JButton("退出");
        exitButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                System.exit(0);
            }
        });
        return exitButton;
    }

    // 加载所有学生成绩
    private void loadAllStudentScores() {
        try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            String query = "SELECT * FROM student_scores";
            try (Statement statement = connection.createStatement()) {
                ResultSet resultSet = statement.executeQuery(query);
                studentScoreListModel.clear();
                while (resultSet.next()) {
                    String studentId = resultSet.getString("student_id");
                    String studentName = resultSet.getString("student_name");
                    double score = resultSet.getDouble("score");
                    studentScoreListModel.addElement(new StudentScore(studentId, studentName, score));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 学生成绩类
    class StudentScore {
        private String studentId;
        private String studentName;
        private double score;

        public StudentScore(String studentId, String studentName, double score) {
            this.studentId = studentId;
            this.studentName = studentName;
            this.score = score;
        }

        @Override
        public String toString() {
            return studentId + " " + studentName + " " + score;
        }
    }
}
